package ncs.student.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ncs.common.database.UserDAO;
import ncs.common.model.User;
import ncs.common.model.exception.UserExistException;
import ncs.common.util.ConnectDB;
import ncs.student.model.Student;

/**
 * @author Jian
 * tb_students
+----------+-------------------+------+-----+---------+-------+
| Field    | Type              | Null | Key | Default | Extra |
+----------+-------------------+------+-----+---------+-------+
| db_id    | int(11)           | NO   | PRI | NULL    |       |
| userID   | varchar(20)       | YES  |     | NULL    |       |
| pwd      | varchar(20)       | NO   |     | NULL    |       |
| name     | varchar(50)       | YES  |     | NULL    |       |
| sex      | enum('男','女')    | YES  |     | NULL    |       |
| birthday | date              | YES  |     | NULL    |       |
| tel      | varchar(50)       | YES  |     | NULL    |       |
| email    | varchar(50)       | YES  |     | NULL    |       |
| qq       | varchar(20)       | YES  |     | NULL    |       |
| addr     | varchar(256)      | YES  |     | NULL    |       |
| comm     | varchar(256)      | YES  |     | NULL    |       |
| grade    | varchar(30)       | YES  |     | NULL    |       |
+----------+-------------------+------+-----+---------+-------+
 */
public class StudentDAO implements IStudentDAO {
	@Override
	public List<Student> getStudents(int start, int limits) throws Exception {
		String sql="select * from tb_student limit ?,?";
		Connection conn=ConnectDB.getConnection();
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, start);
		ps.setInt(2, limits);
		List<Student>collection=new ArrayList<Student>();
		ResultSet rs=ps.executeQuery();
		while(rs.next())
		{
			Student st=new Student();
			st.setDbID(rs.getInt(1));
			st.setUserID(rs.getString(2));
			st.setName(rs.getString(4));
			st.setSex(rs.getString(5));
			st.setBirthday(rs.getString(6));
			st.setTel(rs.getString(7));
			st.setEmail(rs.getString(8));
			st.setQq(rs.getString(9));
			st.setAddr(rs.getString(10));
			st.setGrade(rs.getString(12));
			collection.add(st);
		}
		rs.close();
		ps.close();
		conn.close();
		return collection;
	}

	@Override
	public int getStudentCount() throws Exception {
		Connection conn=ConnectDB.getConnection();
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery("select count(*) from tb_student");
		rs.next();
		int ret=rs.getInt(1);
		rs.close();
		st.close();
		conn.close();
		return ret;
	}

	@Override
	public void addStudent(Student st) throws UserExistException,Exception {
		if(UserDAO.isUserExist(st.getUserID()))
			throw new UserExistException("");
		Connection conn=ConnectDB.getConnection();
		conn.setAutoCommit(false);
		User user=new User();
		user.setAuthority(3);
		user.setPassword(st.getPassword());
		user.setUserID(st.getUserID());
		int key=UserDAO.addUser(user,conn);
		String sql="insert tb_student values(?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setInt(1, key);
		ps.setString(2, st.getUserID());
		ps.setString(3, st.getPassword());
		ps.setString(4, st.getName());
		ps.setString(5, st.getSex());
		ps.setString(6, st.getBirthday());
		ps.setString(7, st.getTel());
		ps.setString(8, st.getEmail());
		ps.setString(9, st.getQq());
		ps.setString(10, st.getAddr());
		ps.setString(11, st.getComm());
		ps.setString(12,st.getGrade());
		try
		{
			ps.execute();
			conn.commit();
		}catch(Exception e)
		{
			conn.rollback();
			ps.close();
			conn.close();
			throw e;
		}
		ps.close();
		conn.close();
	}

	@Override
	public void updateStudentField(int id, String field, Object value)
			throws UserExistException,Exception {
		if(field.equals("userID")&&UserDAO.isUserExist((String)value))
		{
			throw new UserExistException("");
		}
		Connection conn=ConnectDB.getConnection();
		conn.setAutoCommit(false);
		UserDAO userDao=new UserDAO();
		PreparedStatement ps=null;
		try
		{
			if(field.equals("userID")||field.equals("password"))
			{
				userDao.updateField(id, field, value, conn);
			}
			String sql=String.format("update tb_student set %s=? where db_id=?", field);
			ps=conn.prepareStatement(sql);
			ps.setString(1, (String)value);
			ps.setInt(2, id);
			ps.execute();
		}
		catch(Exception e)
		{
			conn.rollback();
			ps.close();
			conn.close();
			throw e;
		}
		conn.commit();
		ps.close();
		conn.close();
	}

	@Override
	public void deleteStudent(int[] id) throws Exception {
		Connection conn=ConnectDB.getConnection();
		conn.setAutoCommit(false);
		UserDAO userDao=new UserDAO();
		try
		{
			userDao.deleteUser(id, conn);
			//如果有其他的再删除
		}catch(Exception e)
		{
			conn.rollback();
			conn.close();
			throw e;
		}
		conn.commit();
		conn.close();
	}

	@Override
	public void deleteStudent(int id) throws Exception {
		int [] idex=new int[1];
		idex[0]=id;
		this.deleteStudent(idex);
	}

}